package com.taoyun.mapper;

import com.taoyun.pojo.dto.DeleteDTO;
import com.taoyun.pojo.entity.File;
import com.taoyun.pojo.entity.ObjectSize;
import com.taoyun.pojo.vo.RecycleFileVO;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 *  操作数据库 文件表相关
 *
 * @author FengJingdong
 */
@Repository
public interface FileMapper {

    /**
     * 判断user与file是否有关联
     *
     * @param userId userId
     * @param fileId fileId
     * @return 0 / 1
     */
    @Select("select count(*) from U_send_F where User_Id = #{userId} AND File_Id = #{fileId} LIMIT 1")
    @ResultType(Integer.class)
    Integer checkRelateByUserIdAndFileId(@Param("userId")Integer userId, @Param("fileId")Integer fileId);

    /**
     * 判断user,group与file是否有关联,且group表的  D_Permission = 1
     *
     * @param userId userId
     * @param fileId fileId
     * @return 0 / 1
     */
    @Select("select count(*) \n" +
            "from \n" +
            "(select u2.User_Id uid,g2.GroupId gid \n" +
            "FROM `group` g2 inner join `user` u2 on g2.U_Id = u2.User_Id\n" +
            "where u2.User_Id = #{userId} and g2.D_Permission = '1' ) t\n" +
            "inner join (select G_Id from F_belong_G where F_Id = #{fileId})fbg \n" +
            "on fbg.G_Id = t.gid")
    @ResultType(Integer.class)
    Integer checkRelateGroup(@Param("userId")Integer userId, @Param("fileId")Integer fileId);

    /**
     * 修改回收字段
     *
     * @param userId 用户主键 id
     * @param fileId 文件id
     * @param selectOpt 回收字段状态
     * @return update sql 的返回值
     */
    @Update("update file set DeleteLogo = #{selectOpt} WHERE File_Id = #{fileId}")
    int putRecycleBin(@Param("userId")Integer userId,
                      @Param("fileId")Integer fileId,@Param("selectOpt")String selectOpt);

    /**
     * 根据fileid 查找obs objectName
     * @param fileId
     * @return
     */
    @Select("select Path_Name pathName,Path objectName, FileSize fileSize from `file` where File_Id = #{fileId} LIMIT 1")
    @ResultType(DeleteDTO.class)
    DeleteDTO findObjectNamesById(@Param("fileId")Integer fileId);

    /**
     * 删除数据库文件记录
     *
     * @param fileId
     * @return
     */
    @Delete("DELETE FROM F_belong_G WHERE F_Id=#{fileId};")
    int deleteFileData(@Param("fileId")Integer fileId);

    @Delete("DELETE FROM U_send_F WHERE File_Id=#{fileId};")
    int deleteFileData2(@Param("fileId")Integer fileId);

    @Delete("DELETE FROM `file` WHERE File_Id=#{fileId};")
    int deleteFileData3(@Param("fileId")Integer fileId);

    /**
     * 浏览回收站信息
     * @param userId 当前用户id
     * @return
     */
    @Select("SELECT f.File_Id fileId , f.File_Name fileName,f.Author author,f.Path path,\n" +
            "f.FileType fileType, f.L_MTime lastTime FROM `file` f WHERE f.DeleteLogo='1' AND f.File_Id IN(\n" +
            "SELECT fbg.F_Id FROM `group` g,F_belong_G fbg \n" +
            "WHERE g.U_Id = #{userId} AND fbg.G_Id = g.GroupId  \n" +
            "AND g.D_Permission = '1'\n" +
            "UNION ALL\n" +
            "SELECT usf.File_Id FROM U_send_F usf WHERE usf.User_Id =#{userId}\n" +
            ")")
    @ResultType(RecycleFileVO.class)
    List<RecycleFileVO> getRecycleBin(@Param("userId")Integer userId);

    /**
     * 查找删除信息 fileIds
     * @param userId 当前用户id
     * @return
     */
    @Select("SELECT `file`.`File_Id` FROM `file`,U_send_F WHERE U_send_F.`File_Id` = `file`.`File_Id` AND U_send_F.`User_Id`=#{userId}")
    @ResultType(Integer.class)
    List<Integer> getFileIdByUserId(@Param("userId")Integer userId);

    /**
     * 根据当前路径名，返回当前路径的文件
     * @param now_path
     * @return
     */
    @Select("select * from file where Path='${now_path}' AND DeleteLogo='0' AND FileType='folder' union all select * from file where Path='${now_path}' AND DeleteLogo='0' AND FileType<>'folder'")
    @ResultType(File.class)
    List<File> ShowFile(@Param("now_path") String now_path);

    /**
     * 根据当前路径，模糊查询文件
     * @param now_path
     * @param file_name
     * @return
     */
    @Select("select * from file where Path_Name LIKE '${now_path}%${file_name}%' AND DeleteLogo='0'")
    @ResultType(File.class)
    List<File> SearchFile_ByName(@Param("now_path") String now_path, @Param("file_name") String file_name);

    /**
     * 根据根目录名(对象名），计算用户所用空间大小
     * @param ObjectName
     * @return
     */
    @Select("select * from ObjectSize where Obsobject_Name='${ObjectName}'")
    @ResultType(ObjectSize.class)
    ObjectSize get_user_data(@Param("ObjectName") String ObjectName);

    /**
     * 根据根目录名(对象名），ObjectSize类
     * @param ObjectName 目录名(对象名）
     * @return
     */
    @Select("select * from ObjectSize where Obsobject_Name='${ObjectName}'")
    @ResultType(ObjectSize.class)
    ObjectSize objectSize(@Param("ObjectName") String ObjectName);

    @Select("select count(*) from ObjectSize where Obsobject_Name='${ObjectName}'")
    @ResultType(Integer.class)
    int ifObjectNameExist(@Param("ObjectName") String ObjectName);

    /**
     * 通过对象名，更新ObjectSize表的used_dataSize
     * @param ObjectName
     * @return 成功返回1，否则返回0
     */
    @Update("update ObjectSize set used_dataSize=(select sum(FileSize) from file where Path like '${ObjectName}%') where obsobject_Name='${ObjectName}'")
    int add_objectData(@Param("ObjectName") String ObjectName);


    /**
     * 通过对象名和删除文件的大小，更新ObjectSize表的used_dataSize
     * @param ObjectName
     * @param size
     * @return 成功返回1，否则返回0
     */
    @Update("update ObjectSize set used_dataSize=used_dataSize-'${size}' where obsobject_Name='${ObjectName}'")
    int reduce_objectData(@Param("ObjectName") String ObjectName,@Param("size") long size);

    /**
     * 新建用户时，在objectSize表新建对象
     * @param ObjectName
     * @param dataSize
     * @return 成功返回1，否则返回0
     */
    @Insert("insert into ObjectSize(Obsobject_Name,used_dataSize,Max_dataSize)" +"VALUES(#{ObjectName},0,#{dataSize})")
    int insert_ObjectSize(@Param("ObjectName") String ObjectName,@Param("dataSize") long dataSize);

    /**
     * 删除用户时，删除他的对象存储空间记录
     * @param ObjectName
     * @return 成功返回1，否则返回0
     */
    @Delete("delete from ObjectSize where Obsobject_Name = #{ObjectName}")
    int delete_ObjectSize(@Param("ObjectName") String ObjectName);

    // 重命名
    @Update("update file set Path=#{filePath},File_Name=#{fileName},Path_Name=#{newObjectName} where Path_Name=#{objectName}")
    void updateObjectName(String objectName,String newObjectName,String fileName,String filePath);

    /**
     * 查询当前用户上传文件时是否存在同名文件在file
     * @param path_Name 当前的文件路径名
     * @return 同名文件条目数
     */
    @Select("select count(*) from file where Path_Name='${path_Name}'")
    @ResultType(Integer.class)
    int countFile(@Param("path_Name") String path_Name);

    /**
     *  查询当前用户上传文件时是否存在用户与文件关系在USendF
     * @param user_Id 用户id
     * @param file_Id 文件id
     * @return int
     */
    @Select("select count(*) from U_send_F where User_Id='${user_Id}' and File_Id='${file_Id}'")
    @ResultType(Integer.class)
    int countUSendF(@Param("user_Id") int user_Id,@Param("file_Id") int file_Id);

    @Select("select count(*) from F_belong_G where F_Id='${F_Id}' and GroupId='${GroupId}'")
    @ResultType(Integer.class)
    int countFbelongF(@Param("F_Id") int F_Id,@Param("GroupId") int GroupId);

    /**
     * //使用当前的文件路径名查询 file_id
     * @param path_Name 当前的文件路径名
     * @return file_id
     */
    @Select("select File_Id from file where Path_Name='${path_Name}'")
    @ResultType(Integer.class)
    int selectFileIdByPathName(@Param("path_Name") String path_Name);


    /**
     * 更新USendF表
     * @param user_Id 用户id
     * @param file_Id 文件id
     * @param creatTime 发布时间
     */
    @Update("UPDATE U_send_F SET SendTime='${creatTime}' WHERE User_Id='${user_Id}' AND File_Id='${file_Id}'")
    void updateUSendF(@Param("user_Id")int user_Id,@Param("file_Id") int file_Id,@Param("creatTime") Date creatTime);




    /**
     * 使用文件路径名查询文件大小
     * @param path  文件路径名
     * @return  FileSize
     */
    @Select("select FileSize from file where Path_name='${path}'")
    @ResultType(Integer.class)
    int selectFileSizeByPathName(String path);

    //插入文件
    @Insert("insert into file values (DEFAULT,#{obsojectName},#{path},#{file_Name},#{path_Name},#{author},#{sharePath},'0',#{creatTime},#{fileType},DEFAULT,#{fileSize})")
    void insertFile(String obsojectName, String path, String file_Name, String path_Name, String author, String sharePath, Date creatTime, String fileType, Integer fileSize);

    //插入文件
    @SelectKey(statement = "select last_insert_id()", before = false, keyProperty = "fileId", resultType = Integer.class)
    @Insert("insert into file(Obsobject_Name, Path, File_Name, Path_Name, Author, SharePath, DeleteLogo, CreateTime, FileType, L_MTime, FileSize) " +
            "values (#{obsobjectName},#{Path},#{fileName},#{pathName},#{Author},#{SharePath},#{DeleteLogo},#{CreateTime},#{FileType},DEFAULT,#{FileSize})")
    void insertFileReturnId(File file);

    //XML配置
    Integer deleteFileBatchs(@Param("fileIds") List<Integer> fileIds);
}
